package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.DictCache;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.controller.NlifePlanConfigController;
import com.sq.partner.manager.dao.CardInfoDao;
import com.sq.partner.manager.dao.XBTSwitchDao;
import com.sq.partner.manager.mybatis.model.CardInfo;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.DictDto;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.XbtSwitchDto;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

/**
 * 激活卡单Dao实现
 * 
 * SQ
 *
 */
@Repository
public class XBTSwitchDaoImpl extends BaseDaoImpl implements XBTSwitchDao {
	private static Logger logger = LogUtil.getLog(XBTSwitchDaoImpl.class);
	@Override
	public Page<CoreDept> queryDeptPageByCriteria(Page<CoreDept> page)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("select t1.dept_code as deptCode, t1.dept_cname as deptCNname from (select t.dept_code, t.dept_cname from t_core_dept t  ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Integer> islike=new ArrayList<Integer>();
			CoreDept coreDept= page.getType();
			
			GatewayUser gatewayuser =  (GatewayUser)page.getObj();
			if(null != gatewayuser){
				if(StrUtil.isNotEmpty(gatewayuser.getDepartmentStr().substring(1,3))){
					sqlbuf.append(" connect by prior dept_code = snr_dept start with dept_code = ? ");
					list.add(gatewayuser.getDepartmentStr().substring(1,3));
					islike.add(0);
				}
			}
			sqlbuf.append("   ) t1 where  1=1 ");//length(t1.dept_code) = 8
			if(StrUtil.isNotEmpty(coreDept.getDeptCode())){
				sqlbuf.append(" and t1.dept_code like ? ");
				list.add(coreDept.getDeptCode());
				islike.add(1);
			}
			if(StrUtil.isNotEmpty(coreDept.getDeptCname())){
				sqlbuf.append(" and t1.dept_cname like ? "); // modified by gzz@20150421 for 出单机构按照 机构名称查询 报错问题
				list.add(coreDept.getDeptCname());
				islike.add(2);
			}
			
					
			sqlbuf.append(" order by t1.dept_code");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)==1){
					pstmt.setObject(i+1, list.get(i)+"%");
				}else if(islike.get(i)==2){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}
				else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)==1){
					pstmt1.setObject(i+1, list.get(i)+"%");
				}else if(islike.get(i)==2){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}
				else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	@Override
	public Page<XbtSwitchDto> querySwichList(Page<XbtSwitchDto> page)
			throws Exception {
		List<XbtSwitchDto> swichs=this.queryList("queryXBTSwitchList", page.getObj()+"%");
		page.setData(swichs);
		return page;
	}
	@Override
	public boolean addXbtSwitchDto(XbtSwitchDto xbtSwitchDto) throws Exception {
		// TODO Auto-generated method stub
		return this.add("insertXBTSwitch", xbtSwitchDto.getDeptCode());
	}
	@Override
	public void deleteXbtSwitchDto(String deptCode) throws Exception {
		 this.delete("deleteXBTSwitch", deptCode);
	}
	@Override
	public void updateAmountLimit(String deptCode, String productCode,
			String loaninvoiceno, String amountLimit,String denyAmountLimit) throws Exception{
		Map<String,Object> map = new HashMap<String, Object>();
		map.put("deptCode",deptCode );
		map.put("productCode", productCode);
		map.put("loanivoiceno", loaninvoiceno);
		map.put("amountLimit", amountLimit);
		map.put("denyAmountLimit", denyAmountLimit);
		map.put("brnoSign", "");
		this.update("updateAmountLimitByDeptCode", map);
	}
	@Override
	public Page<XbtSwitchDto> queryRiskList(Page<XbtSwitchDto> page) throws Exception {
		List<DictDto> dicts = DictCache.getDict("XBTpartners");
		List<String> partnerList = new ArrayList<String>();
		for (DictDto dict : dicts) {
			partnerList.add(dict.getCode());
		}
		//以map的形式传递进db；
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("partners", partnerList);
		map.put("deptCode", page.getObj());
		List<XbtSwitchDto> risks=this.queryList("queryRiskListByDeptCode", map);
		page.setData(risks);
		return page;
	}
	@Override
	public boolean insertAmountLimit(String deptCode, String productCode,
			String loaninvoiceno, String amountLimit,String denyAmountLimit) throws Exception {
		Map<String,Object> map = new HashMap<String, Object>();
		map.put("deptCode",deptCode );
		map.put("productCode", productCode);
		map.put("loanivoiceno", loaninvoiceno);
		map.put("amountLimit", amountLimit);
		map.put("denyAmountLimit", denyAmountLimit);
		map.put("brnoSign", "");
		@SuppressWarnings("unused")
		String amountLimitCount = (String)this.query("queryAmountLimitInfo", map);
		String denyAmountLimitCount = (String)this.query("queryDenyAmountLimitInfo", map);

	/*	Map<String, String> queryMap =(Map<String, String>) this.query("writeBackThisLimit", map);
		String amountLimitCount = queryMap.get("AMOUNTLIMIT");
		String denyAmountLimitCount = queryMap.get("DENYAMOUNTLIMIT");*/

		if(amountLimitCount == null&&denyAmountLimitCount==null){
			this.add("insertAmountLimit", map);
			return true;
		}else if(amountLimitCount == amountLimit && denyAmountLimitCount == denyAmountLimit){
			return true;
		}else{
			return false;
		}
		
	}
	@Override
	public Map<String,String> writeBackThisLimit(Map<String, String> map) throws Exception {
		return  (Map<String, String>) this.query("writeBackThisLimit", map);
	}
	
}
